﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace SPHealth.SharePoint.dbSize.SP.TimerJobs
{
    class DatabaseSizeCollection : SPJobDefinition
    {

        #region Locals
        private const string JobTitle = "SPHealth Database Size Collection";
        private const string JobDescription = "Used to collect content database data and log file sizes. The information collected is used by the SPHealth Review Database Sizes pages to display the growth of content database data and log files over a period of time.";
        private const string dbKeyName = "SPHealth.DBSizeHistory";
        private const string logKeyName = "SPHealth.LogSizeHistory";
        #region SQL queries
        private const string GET_DATABASE_REPORT =
                "begin try " +
                "declare @dbsize bigint " +
                "declare @logsize bigint " +
                "declare @database_size_mb float " +
                "declare @unallocated_space_mb float " +
                "declare @reserved_mb float " +
                "declare @data_mb float " +
                "declare @log_size_mb float " +
                "declare @index_mb float " +
                "declare @unused_mb float " +
                "declare @reservedpages bigint " +
                "declare @pages bigint " +
                "declare @usedpages bigint " +
                " " +
                "select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) " +
                ",@logsize = sum(convert(bigint,case when status & 64 != 0 then size else 0 end)) " +
                "from dbo.sysfiles " +
                " " +
                "select @reservedpages = sum(a.total_pages) " +
                ",@usedpages = sum(a.used_pages) " +
                ",@pages = sum(CASE " +
                "WHEN it.internal_type IN (202,204) THEN 0 " +
                "WHEN a.type != 1 THEN a.used_pages " +
                "WHEN p.index_id < 2 THEN a.data_pages " +
                "ELSE 0 " +
                "END) " +
                "from sys.partitions p " +
                "join sys.allocation_units a on p.partition_id = a.container_id " +
                "left join sys.internal_tables it on p.object_id = it.object_id " +
                " " +
                "select @database_size_mb = (convert(dec (19,2),@dbsize) + convert(dec(19,2),@logsize)) * 8192 / 1048576.0 " +
                "select @unallocated_space_mb =(case " +
                "when @dbsize >= @reservedpages then (convert (dec (19,2),@dbsize) - convert (dec (19,2),@reservedpages)) * 8192 / 1048576.0 " +
                "else 0 " +
                "end) " +
                " " +
                "select  @reserved_mb = @reservedpages * 8192 / 1048576.0 " +
                "select  @data_mb = @pages * 8192 / 1048576.0 " +
                "select  @log_size_mb = convert(dec(19,2),@logsize) * 8192 / 1048576.0 " +
                "select  @index_mb = (@usedpages - @pages) * 8192 / 1048576.0 " +
                "select  @unused_mb = (@reservedpages - @usedpages) * 8192 / 1048576.0 " +
                " " +
                "select " +
                "@database_size_mb as database_size_mb " +
                ",       @reserved_mb as reserved_mb " +
                ",       @unallocated_space_mb as unallocated_space_mb " +
                ",       (@reserved_mb + @unallocated_space_mb) as data_size " +
                ",       @log_size_mb as transaction_log_size " +
                ",       cast(@unallocated_space_mb*100.0/(@reserved_mb + @unallocated_space_mb) as decimal(10,2))as  unallocated " +
                ",       cast(@reserved_mb*100/(@reserved_mb + @unallocated_space_mb) as decimal(10,2))as reserved " +
                ",       cast(@data_mb*100/(@reserved_mb + @unallocated_space_mb) as decimal(10,2))as data " +
                ",       cast(@index_mb*100/(@reserved_mb + @unallocated_space_mb) as decimal(10,2)) as index_1 " +
                ",       cast(@unused_mb*100/(@reserved_mb + @unallocated_space_mb) as decimal(10,2))as unused; " +
                " " +
                "end try " +
                "begin catch " +
                "select " +
                "1 as database_size_mb " +
                ",       ERROR_NUMBER() as reserved_mb " +
                ",       ERROR_SEVERITY() as unallocated_space_mb " +
                ",       ERROR_STATE() as data_size " +
                ",       1 as transaction_log_size " +
                ",       ERROR_MESSAGE() as unallocated " +
                ",       -100 as reserved " +
                ",       1 as data " +
                ",       1 as index_1 " +
                ",       1 as unused " +
                "end catch";

        #endregion

        #endregion

        #region Constructor

        public override string Description
        {
            get { return JobDescription; }
        }
        
        public DatabaseSizeCollection() : base()
        {
            this.Title = JobTitle;
        }
        public DatabaseSizeCollection(string jobname, SPWebApplication webApplication, SPServer server, SPJobLockType lockType) : base(jobname, webApplication, server, lockType)
        {
            this.Title = JobTitle;
        }
        public DatabaseSizeCollection(string jobName, SPService service, SPServer server, SPJobLockType targetType) : base(jobName, service, server, targetType)
        {
        }
        public DatabaseSizeCollection(string jobName, SPWebApplication webApplication) : base(jobName, webApplication, null, SPJobLockType.ContentDatabase)
        {
            this.Title = JobTitle;
        }

        #endregion

        #region Methods
        public override void Execute(Guid contentDbId)
        {
            foreach (SPWebApplication webApplication in SPWebService.ContentService.WebApplications)
            {
                if (webApplication.Status == SPObjectStatus.Online)
                {
                    foreach (SPContentDatabase contentDatabase in webApplication.ContentDatabases)
                    {
                        try
                        {
                            bool collected = GetContentDatabaseStatistics(contentDatabase);
                            if (!collected)
                            {
                                /* best endevours */
                            }
                        }
                        catch (Exception ex)
                        {
                            /* best endevours */
                        }
                    }
                }
            }

        }

        private bool GetContentDatabaseStatistics(SPContentDatabase contentDatabase)
        {
            bool retval = false;
            try
            {
                using (SqlConnection connection = new SqlConnection(contentDatabase.DatabaseConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand { Connection = connection, CommandText = GET_DATABASE_REPORT })
                    {
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader != null && reader.HasRows)
                        {
                            reader.Read();
                            double database_size_mb = Convert.ToDouble(reader["database_size_mb"]);
                            double log_size_mb = Convert.ToDouble(reader["transaction_log_size"]);
                            bool updated = UpdateDatabaseSizeHistory(contentDatabase, database_size_mb, log_size_mb);
                            retval = updated;
                        }
                    }
                }
            }
            catch { /* best endeavours */ }

            return retval;
        }

        private bool UpdateDatabaseSizeHistory(SPContentDatabase contentDatabase, double database_size_mb, double log_size_mb)
        {
            bool retval = false;

            string shortdate = DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.GetCultureInfo("en-US"));

            // update the db size key
            if (contentDatabase.Properties.Contains(dbKeyName))
            {
                contentDatabase.Properties[dbKeyName] = string.Format("{0},{1},{2}", contentDatabase.Properties[dbKeyName].ToString(), shortdate, database_size_mb.ToString());
            }
            else
            {
                contentDatabase.Properties.Add(dbKeyName,string.Format("{0},{1}", shortdate, database_size_mb.ToString()));
            }

            // update the log size key
            if (contentDatabase.Properties.Contains(logKeyName))
            {
                contentDatabase.Properties[logKeyName] = string.Format("{0},{1},{2}", contentDatabase.Properties[logKeyName].ToString(), shortdate, log_size_mb.ToString());
            }
            else
            {
                contentDatabase.Properties.Add(logKeyName, string.Format("{0},{1}", shortdate, log_size_mb.ToString()));
            }
            
            // update changes
            contentDatabase.Update();

            return retval;
        }

        #endregion
    }
}
